package com.qingke.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qingke.pojo.Account;
import com.qingke.pojo.User;


public class StrutsExampleDAO {
	private String driverStr="com.mysql.jdbc.Driver";
	private String connStr="jdbc:mysql://localhost:3306/struts_example?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private String sqlUsername="zjy";
	private String sqlPassword="123456";
	
    private Connection conn=null;		
    private String sql;
    private PreparedStatement ps=null;
    private ResultSet rs=null;  
    
    {
    	try {
			Class.forName(driverStr);
			conn = DriverManager.getConnection(connStr,sqlUsername,sqlPassword);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
    }
    
    public List<User> selectUsers(){
    	List<User> list=new ArrayList<User>();
    	sql="select * from user";
    	try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					User user=new User();
					user.setName(rs.getString("name"));
					user.setAge(rs.getInt("age"));
					user.setHeight(rs.getDouble("height"));
					list.add(user);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return list;
    }
    
    public List<Account> selectAccounts(){
    	List<Account> list=new ArrayList<Account>();
    	sql="select * from account";
    	try {
    		ps=conn.prepareStatement(sql);
    		rs=ps.executeQuery();
    		if(rs!=null){
    			while(rs.next()){
    				Account account=new Account();
    				account.setId(rs.getInt("id"));
    				account.setUsername(rs.getString("username"));
    				account.setPassword(rs.getString("password"));
    				account.setAdmin(rs.getBoolean("isAdmin"));
    				if(!account.isAdmin())
    					list.add(account);
    			}
    		}
    	} catch (SQLException e) {
    		e.printStackTrace();
    	}
    	return list;
    }
    
    public Account login(String username,String password){
    	sql="select * from account where username=? and password=?";
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,username);
			ps.setString(2,password);
			rs=ps.executeQuery();
			if(rs!=null&&rs.next()){
				Account account=new Account();
				account.setId(rs.getInt("id"));
				account.setUsername(username);
				account.setPassword(password);
				account.setAdmin(rs.getBoolean("isAdmin"));
				return account;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    	return null;
    }
    
}
